"""数据库监控指标"""
from common.package.db import MySQL, Oracle, Postgres


class OracleMetric(Oracle):

    InstanceSql = """select count(1) as instance_count from v$instance"""
    MetaSql = """SELECT count(1) as table_count FROM user_tables"""

    StatusSQL = """SELECT METRIC_NAME, VALUE FROM V$SYSMETRIC WHERE GROUP_ID = 2 AND METRIC_NAME IN 
    ('Session Count', 'Average Active Sessions', 'Current OS Load', 'Physical Reads Per Sec', 
    'User Transaction Per Sec', 'Total Table Scans Per Txn', 'Physical Writes Per Sec', 'Total Index Scans Per Txn', 
    'Host CPU Utilization (%)', 'Network Traffic Volume Per Sec', 'User Rollbacks Per Sec', 'CPU Usage Per Sec', 
    'DB Block Changes Per Sec', 'Physical Read Total Bytes Per Sec', 'Response Time Per Txn')"""

    def metric(self):
        indicator = {}
        metas = self.execute_sql(self.InstanceSql)
        metas_list = [dict(row._mapping) for row in metas]
        indicator.update(metas_list[0])

        metas = self.execute_sql(self.MetaSql)
        metas_list = [dict(row._mapping) for row in metas]
        indicator.update(metas_list[0])

        metrics = self.execute_sql(self.StatusSQL)
        metrics_dict = {item['metric_name'].lower().replace(' ', '_'): float(item['value'])
                        for item in [dict(row._mapping) for row in metrics]}
        indicator.update(metrics_dict)
        return indicator


class MySQLMetric(MySQL):
    BuiltinDataBase = ('sys', 'performance_schema', 'information_schema', 'mysql')  # 内置数据库
    Metrics = ('Aborted_clients', 'Aborted_connects', 'Bytes_received', 'Bytes_sent', 'Connections',
               'Created_tmp_tables', 'Innodb_buffer_pool_pages_total', 'Innodb_buffer_pool_pages_free',
               'Innodb_num_open_files', 'Innodb_available_undo_logs', 'Key_blocks_used', 'Key_blocks_unused',
               'Last_query_cost', 'Locked_connects', 'Max_used_connections', 'Queries', 'Slowqueries', 'Uptime'
               )  # 指标太多， 只取重要的几个指标 若还需要其他指标可增加到元组中 TODO

    StatusSQL = """SHOW STATUS;"""
    MetaSql = """SELECT count(distinct(TABLE_SCHEMA)) AS database_count, count(distinct(TABLE_NAME)) AS table_count FROM
     information_schema.tables where TABLE_SCHEMA not in ('sys', 'performance_schema', 'information_schema', 'mysql')
    """

    def metric(self):
        indicator = {}
        metas = self.execute_sql(self.MetaSql)
        metas_list = [dict(row._mapping) for row in metas]
        indicator.update(metas_list[0])

        metrics = self.execute_sql(self.StatusSQL)
        metrics_list = [dict(row._mapping) for row in metrics]

        for metric in metrics_list:
            if metric.get('Variable_name') not in self.Metrics:
                continue
            indicator[metric['Variable_name'].lower()] = float(metric.get('Value', '0'))
        return indicator


class PostgresMetric(Postgres):
    DatabaseSql = """SELECT count(datname) AS database_count  FROM pg_database where datname not in 
        ('postgres', 'template0', 'template1')"""
    TableSql = """SELECT count(tablename) AS table_count FROM pg_tables WHERE schemaname = 'public';"""

    ConnectorSql = """SELECT state, count(1) as count FROM pg_stat_activity group by state"""
    StateSql = """SELECT * FROM pg_stat_bgwriter"""  # 展示指标待梳理 TODO

    def metric(self):
        indicator = {}
        databases = self.execute_sql(self.DatabaseSql)
        databases_list = [dict(row._mapping) for row in databases]
        indicator.update(databases_list[0])

        tables = self.execute_sql(self.TableSql)
        tables_list = [dict(row._mapping) for row in tables]
        indicator.update(tables_list[0])

        connectors = self.execute_sql(self.ConnectorSql)  # 连接状态
        connectors_dict = {item['state']: item['count'] for item in [dict(row._mapping) for row in connectors]}
        indicator['active_connector'] = connectors_dict.get('active', 0)
        indicator['idle_connector'] = connectors_dict.get('idle', 0)

        states = self.execute_sql(self.StateSql)
        states_list = [dict(row._mapping) for row in states]
        indicator.update(states_list[0])

        return indicator


if __name__ == '__main__':
    def mysql_metric():
        mysql = MySQLMetric()
        mysql.init(url='mysql+pymysql://root:password@10.1.1.15:3306/mytest')

        a = mysql.metric()
        print(a)

    def postgres_metric():
        # db2 = DB(url='postgresql+psycopg2://jetty:jettypass@192.168.101.79:5432/postgres', pool_pre_ping=True)
        pg = PostgresMetric(url='postgresql+psycopg2://aiops:aiops@192.168.101.79:5432/aiops', pool_pre_ping=True)

        a = pg.metric()
        print(a)

    def oracle_metric():
        oracle = OracleMetric(url='oracle+cx_oracle://system:oracle@192.168.101.26:1521/?service_name=helowin')

        a = oracle.metric()
        print(a)

    mysql_metric()
    postgres_metric()
    oracle_metric()
